﻿using AchieveCommon;
using AchieveEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace AchieveBLL
{
    public class SaleBLL
    {
        /// <summary>
        /// 销售预测订单的分页查询
        /// </summary>
        public static DataTable getSaleForecastPager(int pageSize, int pageIndex, string whereStr, string order, out int totalCount)
        {
            string orders = "a.FItemID asc";
            string tables = " t_ICItemCustom a left join t_icitembase b  on a.FItemID=b.FItemID left join t_ICITEMCORE c on c.FItemID=a.FItemID";
            string outc = "FItemID, FModel,FName,FNumber,FOrderPrice,F_103,F_104,F_105,FLowLimit";
            string innerc = "a.FItemID, a.F_103,a.F_104,a.F_105,b.FLowLimit,c.FModel,c.FName,c.FNumber,c.FOrderPrice";
            DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3, tables, outc, innerc, pageSize, pageIndex, whereStr, orders, out totalCount);
            return dt;
        }
        /// <summary>
        /// 销售订单的分页查询
        /// </summary>
        public static DataTable getSaleOrderPager(int pageSize, int pageIndex, string whereStr, string order, out int totalCount)
        {
            string orders = "a.FInterID asc";
            string tables = " SEOrder a left join seORDERentry b on a.FInterID=b.FInterID left join t_organization c on c.FItemID=a.FCustID ";
            string outc = "FInterID,FBillNo,FCustID,FName,FDeptID,FQty,FPrice,FAmount,FAuxPriceDiscount,FAllAmount,FHeadSelfS0142,FNote";
            string innerc = "a.FInterID,a.FBillNo,a.FCustID,c.FName,a.FDeptID, b.FQty,b.FPrice,b.FAmount,b.FAuxPriceDiscount,b.FAllAmount,a.FHeadSelfS0142,b.FNote";
            DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3, tables, outc, innerc, pageSize, pageIndex, whereStr, orders, out totalCount);
            return dt;
        }

        public static DataTable getSaleOrderGroupPager(int pageSize, int pageIndex, string whereStr, string order, out int totalCount)
        {
           //1、查主表
//            string tables = @" SEOrder a left join t_organization b on b.FItemID=a.FCustID
//left join t_department c on a.FDeptID=c.FItemID
//left join t_emp d on a.FEmpID=d.FItemID";
//            string outc = "a.FInterID,a.FBillNo,a.FDate,a.FCustID,a.FDeptID,b.FName,c.FName,d.FName";
//            string innerc = "a.FInterID,a.FBillNo,a.FDate,a.FCustID,a.FDeptID,b.FName,c.FName,d.FName";
//            DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3, tables, outc, innerc, pageSize, pageIndex, whereStr, order, out totalCount);
//            return dt;
//           // string fbills = "";

            //string tables = " SEOrder a left join seORDERentry b on a.FInterID=b.FInterID left join t_organization c on c.FItemID=a.FCustID ";
            //string outc = "FInterID,FBillNo,FCustID,FName,FDeptID,FQty,FPrice,FAmount,FAuxPriceDiscount,FAllAmount,FHeadSelfS0142,FNote";
            //string innerc = "a.FInterID,a.FBillNo,a.FCustID,c.FName,a.FDeptID, b.FQty,b.FPrice,b.FAmount,b.FAuxPriceDiscount,b.FAllAmount,a.FHeadSelfS0142,b.FNote";
            //DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3, tables, outc, innerc, pageSize, pageIndex, whereStr, orders, out totalCount);
            //return dt;

            string orders = "a.FDate desc";// order.Replace("FDate", "a.FDate");
            string whereStrInner = whereStr.Replace("FDate", "a.FDate").Replace("FBillNo", "a.FBillNo");
            string tables = @" SEOrder a left join seORDERentry b on a.FInterID=b.FInterID left join t_organization c on c.FItemID=a.FCustID
left join ICSTOCKBILLentry d on d.fsourcebillno=a.FBillNo
left join icsaleEntry e on e.fOrderbillno=a.FBillNo
left join t_department f on a.FDeptID=f.FItemID
left join t_emp g on a.FEmpID=g.FItemID ";
            string outColumns = "FDate,dept,emp,FBillNo,kehu,jiashui,fahuo,kaipiao ";
            string innerColumns = @"a.FDate FDate,f.FName dept, g.FName emp,a.FBillNo FBillNo, c.FName kehu,
sum(b.FAllAmount) jiashui, sum(d.fconsignAmount) fahuo,sum(e.FAllAmount) kaipiao";
            SqlParameter[] paras = { 
                                   new SqlParameter("@tableName",SqlDbType.NVarChar,500),
                                   new SqlParameter("@outColumns",SqlDbType.NVarChar,1000),
                                   new SqlParameter("@order",SqlDbType.NVarChar,500),
                                   new SqlParameter("@pageSize",SqlDbType.Int),
                                   new SqlParameter("@pageIndex",SqlDbType.Int),
                                   new SqlParameter("@whereCount",SqlDbType.NVarChar,2000),
                                   new SqlParameter("@where",SqlDbType.NVarChar,2000),
                                   new SqlParameter("@innerColumns",SqlDbType.NVarChar,1000),
                                   new SqlParameter("@totalCount",SqlDbType.Int)                                   
                                   };
            paras[0].Value = tables;
            paras[1].Value = outColumns;
            paras[2].Value = orders;
            paras[3].Value = pageSize;
            paras[4].Value = pageIndex;
            paras[5].Value = whereStr;
            paras[6].Value = whereStrInner;
            paras[7].Value = innerColumns;
            paras[8].Direction = ParameterDirection.Output;   //输出参数
            paras[8].IsNullable = false;
            string sql = @"declare @beginIndex int,@endIndex int,@sqlresult nvarchar(2000),@sqlGetCount nvarchar(2000)
                            set @beginIndex = (@pageIndex - 1) * @pageSize + 1
                            set @endIndex = (@pageIndex) * @pageSize
                            set @sqlresult = 'select '+@outColumns+' from (
                            select row_number() over(order by '+ @order +')
                            as Rownum,'+@innerColumns+'
                            from '+@tableName+' where '+ @where +' group by  a.FDate,a.FBillNo,c.FName,f.FName, g.FName) as T
                            where T.Rownum between ' + CONVERT(varchar(max),@beginIndex) + ' and ' + CONVERT(varchar(max),@endIndex);
                            set @sqlGetCount = 'select @totalCount = count(*) from SEOrder where ' + @whereCount
                            exec(@sqlresult)
                            exec sp_executesql @sqlGetCount,N'@totalCount int output',@totalCount output";
            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql, paras);
            totalCount = Convert.ToInt32(paras[8].Value);   //赋值输出参数，即当前记录总数
            return dt;
            
            
            
            //DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3, tables, outc, innerc, pageSize, pageIndex, whereStr, orders, out totalCount);
            //return dt;

            //throw new NotImplementedException();
        }

        public static DataTable getSaleOrderMainPager(int pageSize, int pageIndex, string whereStr, string order, out int totalCount)
        {
            //1、查主表
            string tables = @" SEOrder a left join t_organization b on b.FItemID=a.FCustID
            left join t_department c on a.FDeptID=c.FItemID
            left join t_emp d on a.FEmpID=d.FItemID";
            string outc = "FInterID,FBillNo,FDate,custName,deptName,empName";
            string innerc = "a.FInterID,a.FBillNo,a.FDate,a.FCustID,a.FDeptID,b.FName custName,c.FName deptName,d.FName empName";
            DataTable dt = AchieveCommon.SqlPagerHelper.GetPagerBySQL(SqlHelper.connStrK3, tables, outc, innerc, pageSize, pageIndex, whereStr, order, out totalCount);
            return dt; 
        }
        public static DataTable getSaleOrderAmountPager(int pageSize, int pageIndex, string whereStr, string order, out int totalCount)
        {
            DataTable dt =getSaleOrderMainPager(pageSize, pageIndex, whereStr,  order, out  totalCount);
            //附加汇总金额信息
            string fbillnos = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
               fbillnos+= dt.Rows[i]["FBillNo"].ToString()+",";
            }
            fbillnos.TrimEnd(',');
            fbillnos += ")";
            string sql = @"select a.FBillNo,
 sum(b.FAllAmount) as jiashui, sum(c.fconsignAmount) fahuo,sum(d.FAllAmount) as kaipiao
from  SEOrder a left join seORDERentry b on a.FInterID=b.FInterID 
left join ICSTOCKBILLentry  c on c.fsourcebillno=a.FBillNo
left join icsaleEntry d on d.fOrderbillno=a.FBillNo
where a.FBillNo in {0} and d.fsourcetrantype=81
group by a.FBillNo";
            string.Format(sql, fbillnos);
            return dt; 
        
        }

    }
}
